package com.fr.data;

import com.fr.base.Parameter;
import com.fr.general.data.TableDataException;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.*;

public class SalesReportWeekCity extends AbstractTableData {
    // 字段名枚举
    enum FIELD_NAME {
        YEAR, MONTH, DAY, WEEK_DAY,
        //全国参数
        SALES_AMOUNT, CUSTOMER_AMOUNT, COPIES, STORE_AMOUNT,

        SALES_AMOUNT_LAST_WEEK_ALL, CUSTOMER_AMOUNT_LAST_WEEK_ALL, COPIES_LAST_WEEK_ALL, STORE_AMOUNT_LAST_WEEK_ALL,
        SALES_AMOUNT_LAST_YEAR_ALL, CUSTOMER_AMOUNT_LAST_YEAR_ALL, COPIES_LAST_YEAR_ALL, STORE_AMOUNT_LAST_YEAR_ALL,
        //老店参数
        SALES_AMOUNT_OLD, CUSTOMER_AMOUNT_OLD, COPIES_OLD, STORE_AMOUNT_OLD,

        SALES_AMOUNT_OLD_LAST_WEEK_ALL, CUSTOMER_AMOUNT_OLD_LAST_WEEK_ALL, COPIES_OLD_LAST_WEEK_ALL, STORE_AMOUNT_OLD_LAST_WEEK_ALL,
        SALES_AMOUNT_OLD_LAST_YEAR_ALL, CUSTOMER_AMOUNT_OLD_LAST_YEAR_ALL, COPIES_OLD_LAST_YEAR_ALL, STORE_AMOUNT_OLD_LAST_YEAR_ALL
    }

    // 列名数组，保存程序数据集所有列名
    private String[] columNames;
    // 保存查询得到列值
    private LinkedHashSet<LinkedHashMap<String, Object>> rowData;

    // 构造函数，定义表结构，该表有10个数据列，列名为column#0，column#1，。。。。。。column#9
    public SalesReportWeekCity() {
        // 定义tableName参数
//        setDefaultParameters(new Parameter[]{new Parameter("tableName")});
        // 定义程序数据集列名
        this.parameters = new Parameter[]{new Parameter("date"), new Parameter("city")};
        // 填充字段名
        columNames = new String[FIELD_NAME.values().length];
        int i = 0;
        for (FIELD_NAME fieldName : FIELD_NAME.values()) {
            columNames[i] = fieldName.toString();
            i++;
        }
    }

    @Override
    public int getColumnCount() throws TableDataException {
        return columNames.length;
    }

    @Override
    public String getColumnName(int columnIndex) throws TableDataException {
        return columNames[columnIndex];
    }

    @Override
    public int getRowCount() throws TableDataException {
        queryData();
        return rowData.size();
    }

    @Override
    public Object getValueAt(int rowIndex, int columnIndex) {
        queryData();
        int tempRowIndex = 0;
        for (LinkedHashMap<String, Object> row : rowData) {
            if (tempRowIndex == rowIndex) {
                return row.get(columNames[columnIndex]);
            }
            tempRowIndex += 1;
        }
        return null;
    }

    public static String checkStringLength(String s) {
        if (s.length() == 1) {
            s = "0" + s;
            return s;
        }
        return s;
    }

    /**
     *  获取数据库连接 driverName和 url 可以换成您需要的
     * @return
     */
    public static Connection getConnection() {

        String driverName = "oracle.jdbc.driver.OracleDriver";
        String url = "jdbc:oracle:thin:@10.10.201.97:1521:crmngpsd";
        String username = "zyngprd";
        String password = "zyngprd";
        Connection con = null;
        try {
            Class.forName(driverName);
            con = DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
        return con;
    }
    // 准备数据
    public void queryData() {
        // 确保只被执行一次
        if (rowData != null) {
            return;
        }
        // 保存得到的数据库表名
        String yearForQuery = parameters[0].getValue().toString();
        String city = parameters[1].getValue().toString();

//        String yearForQuery = "Thu Sep 13 2018 00:00:00 GMT+0800";
//        String city = "1-1";
        String week = "01";
//        String yearForQuery = "";
        String queryDate = "20180101";
        String lastYearQueryDate = "20170101";
        int length = yearForQuery.length();
        if (length > 4) {
            Date date1 = new Date(yearForQuery);
            yearForQuery = new SimpleDateFormat("yyyy").format(date1);
            Calendar ca = Calendar.getInstance();
            ca.setTime(date1);
            ca.add(Calendar.DATE, -365);
            Date time = ca.getTime();
            queryDate = new SimpleDateFormat("yyyyMMdd").format(time);
            //前年
            ca.add(Calendar.DATE, -365);
            lastYearQueryDate = new SimpleDateFormat("yyyyMMdd").format(ca.getTime());
            //日期转换为当年所处的周数
            Calendar calendar = Calendar.getInstance();
            calendar.setFirstDayOfWeek(Calendar.MONDAY);
            calendar.setTime(date1);
            week = String.valueOf(calendar.get(Calendar.WEEK_OF_YEAR));
            week = checkStringLength(week);
        }

        // 构造SQL语句,并打印出来
        //查询老店经销商Id
        StringBuffer oldStorePartyId = new StringBuffer();
        oldStorePartyId.append("SELECT s.party_id FROM zdev_store_info s ");
        oldStorePartyId.append(" inner join (");
        oldStorePartyId.append("  SELECT  V2.YEAR, V2.MONTH, v2.day FROM ZDEV_SALES_H_V2 V2 WHERE v2.dim_type = '01'  AND v2.dim_id = '" + city + "' AND v2.year = '" + yearForQuery + "' AND v2.week = '" + week + "' GROUP BY V2.YEAR, V2.MONTH,v2.day");
        oldStorePartyId.append(" ) T on t.year = s.year and t.month = s.month and t.day = s.day");
        oldStorePartyId.append(" WHERE s.store_status_id = 'OPEN' AND s.city= '" + city + "' GROUP BY s.party_id ");
        // 保存得到的结果集
        rowData = new LinkedHashSet<LinkedHashMap<String, Object>>();
        // 下面开始建立数据库连接，按照刚才的SQL语句进行查询
        Connection conn = getConnection();
        try {
            Statement stmt = conn.createStatement();

            //==========================================今年本周数据
            List yearWeekDataList = new ArrayList();
            //查询今年本周本城市数据
            StringBuffer yearWeekData = new StringBuffer();
            yearWeekData.append("  SELECT  V2.YEAR, V2.MONTH, v2.day,  v2.week_day,SUM(V2.SALES_AMOUNT) QUANTITY, ");
            yearWeekData.append(" SUM（V2.QUANTITY1）CUSTOMER_AMOUNT, SUM (V2.STORE_AMOUNT) STORE_AMOUNT,SUM(V2.QUANTITY2) COPIES");
            yearWeekData.append("  FROM ZDEV_SALES_H_V2 V2");
            yearWeekData.append(" WHERE v2.dim_type = '01'  AND v2.dim_id = '" + city + "' AND v2.year = '" + yearForQuery + "' AND v2.week = '" + week + "'");
            yearWeekData.append(" GROUP BY V2.YEAR, V2.MONTH,v2.day,  v2.week_day");
            yearWeekData.append("  ORDER BY v2.year DESC,v2.month DESC ,v2.day DESC ");
            ResultSet yearWeekRs = stmt.executeQuery(yearWeekData.toString());
            //今年本周
            LinkedHashMap<String, Object> thisYearWeekMap;
            while (yearWeekRs.next()) {
                thisYearWeekMap = new LinkedHashMap();
                thisYearWeekMap.put(FIELD_NAME.YEAR.toString(), yearWeekRs.getString("YEAR"));
                thisYearWeekMap.put(FIELD_NAME.MONTH.toString(), yearWeekRs.getString("MONTH"));
                thisYearWeekMap.put(FIELD_NAME.DAY.toString(), yearWeekRs.getString("DAY"));
                thisYearWeekMap.put(FIELD_NAME.WEEK_DAY.toString(), yearWeekRs.getString("WEEK_DAY"));
                thisYearWeekMap.put(FIELD_NAME.SALES_AMOUNT.toString(), yearWeekRs.getBigDecimal("QUANTITY"));
                thisYearWeekMap.put(FIELD_NAME.CUSTOMER_AMOUNT.toString(), yearWeekRs.getBigDecimal("CUSTOMER_AMOUNT"));
                thisYearWeekMap.put(FIELD_NAME.COPIES.toString(), yearWeekRs.getBigDecimal("COPIES"));
                thisYearWeekMap.put(FIELD_NAME.STORE_AMOUNT.toString(), yearWeekRs.getString("STORE_AMOUNT"));
                yearWeekDataList.add(thisYearWeekMap);
            }
            yearWeekRs.close();
            // ==========================================今年上周数据
            List lastWeekDataList = new ArrayList();
            String thisYear = "";
            String weekDay2 = "";
            //查询上周数据
            if ("01".equals(week)) {
                weekDay2 = "52";
                thisYear = String.valueOf(Integer.parseInt(yearForQuery) - 1);
            } else {
                thisYear = yearForQuery;
                weekDay2 = String.valueOf(Integer.parseInt(week) - 1);
                weekDay2 = checkStringLength(weekDay2);
            }
            StringBuffer yearLastWeekData = new StringBuffer();
            yearLastWeekData.append("  SELECT  V2.YEAR, V2.MONTH, v2.day,  v2.week_day,SUM(V2.SALES_AMOUNT) QUANTITY, ");
            yearLastWeekData.append(" SUM（V2.QUANTITY1）CUSTOMER_AMOUNT,SUM (V2.STORE_AMOUNT) STORE_AMOUNT,SUM(V2.QUANTITY2) COPIES ");
            yearLastWeekData.append("  FROM ZDEV_SALES_H_V2 V2");
            yearLastWeekData.append(" WHERE v2.dim_type = '01'  AND v2.dim_id = '" + city + "'  AND v2.year = '" + thisYear + "' AND v2.week = '" + weekDay2 + "'");
            yearLastWeekData.append(" GROUP BY V2.YEAR, V2.MONTH,v2.day,  v2.week_day");
            yearLastWeekData.append("  ORDER BY v2.year DESC,v2.month DESC ,v2.day DESC ");
            ResultSet lastWeekRs = stmt.executeQuery(yearLastWeekData.toString());
            LinkedHashMap<String, Object> thisYearLastWeekMap;
            //上周参数算合计
            BigDecimal salesAmountLastWeekAll = new BigDecimal(0);
            BigDecimal customerAmountLastWeekAll = new BigDecimal(0);
            BigDecimal copiesLastWeekAll = new BigDecimal(0);
            BigDecimal storeAmountLastWeekAll = new BigDecimal(0);
//            QUANTITY_LAST_WEEK, CUSTOMER_AMOUNT_LAST_WEEK, COPIES_LAST_WEEK, DAY_STORE_AMOUNT_LAST_WEEK, UNIT_PRICE_LAST_WEEK,
            while (lastWeekRs.next()) {
                //合计
                salesAmountLastWeekAll = salesAmountLastWeekAll.add(lastWeekRs.getBigDecimal("QUANTITY"));
                customerAmountLastWeekAll = customerAmountLastWeekAll.add(lastWeekRs.getBigDecimal("CUSTOMER_AMOUNT"));
                copiesLastWeekAll = copiesLastWeekAll.add(lastWeekRs.getBigDecimal("COPIES"));
                storeAmountLastWeekAll = storeAmountLastWeekAll.add(lastWeekRs.getBigDecimal("STORE_AMOUNT"));
            }
            lastWeekRs.close();

            // ==========================================去年数据
            //查询去年数据
            StringBuffer yearWeekOldCityData = new StringBuffer();
            //去年
            String lastYearForQuery = String.valueOf(Integer.parseInt(yearForQuery) - 1);
            StringBuffer yearLastYearData = new StringBuffer();
            yearLastYearData.append("  SELECT  V2.YEAR, V2.MONTH, v2.day,  v2.week_day,SUM(V2.SALES_AMOUNT) QUANTITY, ");
            yearLastYearData.append(" SUM（V2.QUANTITY1）CUSTOMER_AMOUNT,SUM (V2.STORE_AMOUNT) STORE_AMOUNT,SUM(V2.QUANTITY2) COPIES");
            yearLastYearData.append("  FROM ZDEV_SALES_H_V2 V2");
            yearLastYearData.append(" WHERE v2.dim_type = '01'  AND v2.dim_id = '" + city + "'  AND v2.year = '" + lastYearForQuery + "' AND v2.week = '" + week + "'");
            yearLastYearData.append(" GROUP BY V2.YEAR, V2.MONTH,v2.day,  v2.week_day");
            yearLastYearData.append("  ORDER BY v2.year DESC,v2.month DESC ,v2.day DESC ");
            ResultSet lastYearRs = stmt.executeQuery(yearLastYearData.toString());
            //去年参数算合计
            BigDecimal salesAmountLastYearAll = new BigDecimal(0);
            BigDecimal customerAmountLastYearAll = new BigDecimal(0);
            BigDecimal copiesLastYearAll = new BigDecimal(0);
            BigDecimal storeAmountLastYearAll = new BigDecimal(0);
//            QUANTITY_LAST_WEEK, CUSTOMER_AMOUNT_LAST_WEEK, COPIES_LAST_WEEK, DAY_STORE_AMOUNT_LAST_WEEK, UNIT_PRICE_LAST_WEEK,
            while (lastYearRs.next()) {
                //合计
                salesAmountLastYearAll = salesAmountLastYearAll.add(lastYearRs.getBigDecimal("QUANTITY"));
                customerAmountLastYearAll = customerAmountLastYearAll.add(lastYearRs.getBigDecimal("CUSTOMER_AMOUNT"));
                copiesLastYearAll = copiesLastYearAll.add(lastYearRs.getBigDecimal("COPIES"));
                storeAmountLastYearAll = storeAmountLastYearAll.add(lastYearRs.getBigDecimal("STORE_AMOUNT"));
            }
            lastYearRs.close();
            //合并今年本周、上周、去年本周数据
            for (int i = 0; i < yearWeekDataList.size(); i++) {
                LinkedHashMap<String, Object> map1 = (LinkedHashMap<String, Object>) yearWeekDataList.get(i);
                //本周、上周合并
                // SALES_AMOUNT_LAST_WEEK_ALL, CUSTOMER_AMOUNT_LAST_WEEK_ALL, COPIES_LAST_WEEK_ALL, STORE_AMOUNT_LAST_WEEK_ALL, UNIT_PRICE_LAST_WEEK_ALL,
                map1.put(FIELD_NAME.SALES_AMOUNT_LAST_WEEK_ALL.toString(), salesAmountLastWeekAll);
                map1.put(FIELD_NAME.CUSTOMER_AMOUNT_LAST_WEEK_ALL.toString(), customerAmountLastWeekAll);
                map1.put(FIELD_NAME.COPIES_LAST_WEEK_ALL.toString(), copiesLastWeekAll);
                map1.put(FIELD_NAME.STORE_AMOUNT_LAST_WEEK_ALL.toString(), storeAmountLastWeekAll);
                //今年本周，去年本周合并
                //SALES_AMOUNT_OLD_LAST_WEEK_ALL, CUSTOMER_AMOUNT_OLD_LAST_WEEK_ALL, COPIES_OLD_LAST_WEEK_ALL, STORE_AMOUNT_OLD_LAST_WEEK_ALL, UNIT_PRICE_OLD_LAST_WEEK_ALL,
                map1.put(FIELD_NAME.SALES_AMOUNT_LAST_YEAR_ALL.toString(), salesAmountLastYearAll);
                map1.put(FIELD_NAME.CUSTOMER_AMOUNT_LAST_YEAR_ALL.toString(), customerAmountLastYearAll);
                map1.put(FIELD_NAME.COPIES_LAST_YEAR_ALL.toString(), copiesLastYearAll);
                map1.put(FIELD_NAME.STORE_AMOUNT_LAST_YEAR_ALL.toString(), storeAmountLastYearAll);
            }

            //==============================老店（当前日期365天之前就存在的店算老店）
            List partyIdList = new ArrayList();
            //查询经销商
            ResultSet resultSet = stmt.executeQuery(oldStorePartyId.toString());
            while (resultSet.next()) {
                partyIdList.add(resultSet.getString("party_id"));
            }
            resultSet.close();


            //根据查询出的经销商查询具体的表数据
            List yearWeekOldList = new ArrayList();
            List yearWeekOldListForAdd = new ArrayList();

            //老店上周参数
            BigDecimal salesAmountOldLastWeek = new BigDecimal(0);
            BigDecimal customerAmountOldLastWeek = new BigDecimal(0);
            BigDecimal copiesOldLastWeek = new BigDecimal(0);
            BigDecimal storeAmountOldLastWeek = new BigDecimal(0);
            //老店上年本周合计
            BigDecimal salesAmountOldLastYear = new BigDecimal(0);
            BigDecimal customerAmountOldLastYear = new BigDecimal(0);
            BigDecimal copiesOldLastYear = new BigDecimal(0);
            BigDecimal storeAmountOldLastYear = new BigDecimal(0);
            for (int i = 0; i < partyIdList.size(); i++) {
                //判断表是否存在
                String tableExist = "select count(1) from user_tables where table_name = 'ZDEV_SALES_DTL" + partyIdList.get(i) + "_V2'";
                ResultSet tableExistResultSet = stmt.executeQuery(tableExist);
                String tableExistFlag = "";
                while (tableExistResultSet.next()) {
                    tableExistFlag = tableExistResultSet.getString(1);
                }
                if ("1".equals(tableExistFlag)) {
                    //==========今年本周老店
                    // 查询本年本周老店数据
                    StringBuffer yearWeekOldData = new StringBuffer();
                    //各经销商本年本周老店数据
                    yearWeekOldData.append(" SELECT T . YEAR,T . MONTH,T . DAY,T .week_day,T .QUANTITY,T .CUSTOMER_AMOUNT,T .COPIES,COUNT (ps.STORE_ID) STORE_AMOUNT ");
                    yearWeekOldData.append(" FROM (");
                    yearWeekOldData.append("  SELECT  V2.YEAR, V2.MONTH, v2.day,  v2.week_day,SUM(V2.SALES_AMOUNT) QUANTITY,SUM(V2.QUANTITY1) CUSTOMER_AMOUNT, SUM(V2.QUANTITY2) COPIES ");
                    yearWeekOldData.append(" FROM zdev_sales_dtl" + partyIdList.get(i) + "_v2 V2");
                    yearWeekOldData.append(" WHERE v2.dim_type = '01' AND V2.YEAR = '" + yearForQuery + "' AND v2.week = '" + week + "'");
                    yearWeekOldData.append(" GROUP BY V2.YEAR, V2.MONTH,v2.day,  v2.week_day  ");
                    yearWeekOldData.append(" ) T");
                    yearWeekOldData.append(" INNER JOIN (SELECT s.store_id,s. YEAR,s. MONTH,s. DAY FROM zdev_sales_dtl" + partyIdList.get(i) + "_v2 s GROUP BY s.store_id ,s. YEAR,s. MONTH,s. DAY ) ps ON ps.YEAR = (T.YEAR - 1) AND ps.MONTH = T.MONTH AND PS.DAY  = T.DAY");
                    yearWeekOldData.append(" GROUP BY T . YEAR,T . MONTH,T . DAY,T .week_day,T .QUANTITY,T .CUSTOMER_AMOUNT,T .COPIES");
                    ResultSet yearWeekOldRs = stmt.executeQuery(yearWeekOldData.toString());
                    LinkedHashMap<String, Object> map;
                    while (yearWeekOldRs.next()) {
                        map = new LinkedHashMap();
                        String year1 = yearWeekOldRs.getString("YEAR");
                        String month1 = yearWeekOldRs.getString("MONTH");
                        String day1 = yearWeekOldRs.getString("DAY");
                        map.put(FIELD_NAME.YEAR.toString(), year1);
                        map.put(FIELD_NAME.MONTH.toString(), month1);
                        map.put(FIELD_NAME.DAY.toString(), day1);
                        map.put(FIELD_NAME.WEEK_DAY.toString(), yearWeekOldRs.getString("WEEK_DAY"));
                        map.put(FIELD_NAME.SALES_AMOUNT_OLD.toString(), yearWeekOldRs.getBigDecimal("QUANTITY"));
                        map.put(FIELD_NAME.CUSTOMER_AMOUNT_OLD.toString(), yearWeekOldRs.getBigDecimal("CUSTOMER_AMOUNT"));
                        map.put(FIELD_NAME.COPIES_OLD.toString(), yearWeekOldRs.getBigDecimal("COPIES"));
                        map.put(FIELD_NAME.STORE_AMOUNT_OLD.toString(), yearWeekOldRs.getBigDecimal("STORE_AMOUNT"));
                        boolean flag = true;
                        for (int j = 0; j < yearWeekOldList.size(); j++) {
                            LinkedHashMap<String, Object> map1 = (LinkedHashMap) yearWeekOldList.get(j);
                            String year = (String) map1.get(FIELD_NAME.YEAR.toString());
                            String month = (String) map1.get(FIELD_NAME.MONTH.toString());
                            String day = (String) map1.get(FIELD_NAME.DAY.toString());
                            if (year.equals(year1) && month.equals(month1) && day.equals(day1)){
                                BigDecimal salesAmount = (BigDecimal) map1.get(FIELD_NAME.SALES_AMOUNT_OLD.toString());
                                salesAmount =salesAmount.add(yearWeekOldRs.getBigDecimal("QUANTITY"));
                                BigDecimal customerAmount = (BigDecimal) map1.get(FIELD_NAME.CUSTOMER_AMOUNT_OLD.toString());
                                customerAmount =customerAmount.add(yearWeekOldRs.getBigDecimal("CUSTOMER_AMOUNT"));
                                BigDecimal copies = (BigDecimal) map1.get(FIELD_NAME.COPIES_OLD.toString());
                                copies = copies.add(yearWeekOldRs.getBigDecimal("COPIES"));
                                BigDecimal storeAmout = (BigDecimal) map1.get(FIELD_NAME.STORE_AMOUNT_OLD.toString());
                                storeAmout = storeAmout.add(yearWeekOldRs.getBigDecimal("STORE_AMOUNT"));
                                map1.put(FIELD_NAME.SALES_AMOUNT_OLD.toString(), salesAmount);
                                map1.put(FIELD_NAME.CUSTOMER_AMOUNT_OLD.toString(), customerAmount);
                                map1.put(FIELD_NAME.COPIES_OLD.toString(), copies);
                                map1.put(FIELD_NAME.STORE_AMOUNT_OLD.toString(), storeAmout);
                                flag = false;
                            }
                        }
                        if (flag) {
                            yearWeekOldList.add(map);
                        }
                    }
                    yearWeekOldRs.close();
                    //==========今年上周老店
                    //查询今年上周老店数量数据
                    String thisYearOld = "";
                    String weekDay2Old = "";
                    //查询上周数据
                    if ("01".equals(week)) {
                        weekDay2Old = "52";
                        thisYearOld = String.valueOf(Integer.parseInt(yearForQuery) - 1);
                    } else {
                        thisYearOld = yearForQuery;
                        weekDay2Old = String.valueOf(Integer.parseInt(week) - 1);
                        weekDay2Old = checkStringLength(weekDay2Old);
                    }
                    // 查询本年上周老店数据
                    StringBuffer yearLastWeekOldData = new StringBuffer();
                    //各经销商本年上周老店数据
                    yearLastWeekOldData.append(" SELECT T . YEAR,T . MONTH,T . DAY,T .week_day,T .QUANTITY,T .CUSTOMER_AMOUNT,T .COPIES,COUNT (ps.STORE_ID) STORE_AMOUNT ");
                    yearLastWeekOldData.append(" FROM (");
                    yearLastWeekOldData.append("  SELECT  V2.YEAR, V2.MONTH, v2.day,  v2.week_day,SUM(V2.SALES_AMOUNT) QUANTITY,SUM(V2.QUANTITY1) CUSTOMER_AMOUNT, SUM(V2.QUANTITY2) COPIES ");
                    yearLastWeekOldData.append("  FROM zdev_sales_dtl" + partyIdList.get(i) + "_v2 V2");
                    yearLastWeekOldData.append("  WHERE v2.dim_type = '01'  AND V2.YEAR = '" + thisYearOld + "'  AND v2.dim_id = '" + city + "'  AND v2.week = '" + weekDay2Old + "'");
                    yearLastWeekOldData.append("  GROUP BY V2.YEAR, V2.MONTH,v2.day,  v2.week_day  ");
                    yearLastWeekOldData.append(" ) T");
                    yearLastWeekOldData.append(" INNER JOIN (SELECT s.store_id ,s. YEAR,s. MONTH,s. DAY FROM zdev_sales_dtl" + partyIdList.get(i) + "_v2 s GROUP BY s.store_id ,s. YEAR,s. MONTH,s. DAY ) ps ON ps.YEAR = (T.YEAR - 1) AND ps.MONTH = T.MONTH AND PS.DAY  = T.DAY");
                    yearLastWeekOldData.append(" GROUP BY T . YEAR,T . MONTH,T . DAY,T .week_day,T .QUANTITY,T .CUSTOMER_AMOUNT,T .COPIES");
                    ResultSet yearLastWeekOldRs = stmt.executeQuery(yearLastWeekOldData.toString());
                    while (yearLastWeekOldRs.next()) {
                        salesAmountOldLastWeek = salesAmountOldLastWeek.add(yearLastWeekOldRs.getBigDecimal("QUANTITY"));
                        customerAmountOldLastWeek = customerAmountOldLastWeek.add(yearLastWeekOldRs.getBigDecimal("CUSTOMER_AMOUNT"));
                        copiesOldLastWeek = copiesOldLastWeek.add(yearLastWeekOldRs.getBigDecimal("COPIES"));
                        storeAmountOldLastWeek = storeAmountOldLastWeek.add(yearLastWeekOldRs.getBigDecimal("STORE_AMOUNT"));
                    }
                    yearLastWeekOldRs.close();
                    //==========去年本周老店
                    //查询去年本周老店数量数据
                    String lastYearOld = String.valueOf(Integer.parseInt(yearForQuery) - 1);
                    // 查询去年本周老店数据
                    StringBuffer lastYearWeekOldData = new StringBuffer();
                    //各经销商去年本周老店数据
                    lastYearWeekOldData.append(" SELECT T . YEAR,T . MONTH,T . DAY,T .week_day,T .QUANTITY,T .CUSTOMER_AMOUNT,T .COPIES,COUNT (ps.STORE_ID) STORE_AMOUNT ");
                    lastYearWeekOldData.append(" FROM (");
                    lastYearWeekOldData.append("  SELECT  V2.YEAR, V2.MONTH, v2.day,  v2.week_day,SUM(V2.SALES_AMOUNT) QUANTITY,SUM(V2.QUANTITY1) CUSTOMER_AMOUNT, SUM(V2.QUANTITY2) COPIES ");
                    lastYearWeekOldData.append("  FROM zdev_sales_dtl" + partyIdList.get(i) + "_v2 V2");
                    lastYearWeekOldData.append("  WHERE v2.dim_type = '01' AND V2.YEAR = '" + lastYearOld + "' AND v2.week = '" + week + "'");
                    lastYearWeekOldData.append("  GROUP BY V2.YEAR, V2.MONTH,v2.day,  v2.week_day  ");
                    lastYearWeekOldData.append(" ) T");
                    lastYearWeekOldData.append(" INNER JOIN (SELECT s.store_id,s. YEAR,s. MONTH,s. DAY FROM zdev_sales_dtl" + partyIdList.get(i) + "_v2 s GROUP BY s.store_id,s. YEAR,s. MONTH,s. DAY ) ps ON ps.YEAR = T.YEAR  AND ps.MONTH = T.MONTH AND PS.DAY  = T.DAY");
                    lastYearWeekOldData.append(" GROUP BY T . YEAR,T . MONTH,T . DAY,T .week_day,T .QUANTITY,T .CUSTOMER_AMOUNT,T .COPIES");
                    ResultSet lastYearWeekOldRs = stmt.executeQuery(lastYearWeekOldData.toString());
                    while (lastYearWeekOldRs.next()) {
                        salesAmountOldLastYear = salesAmountOldLastYear.add(lastYearWeekOldRs.getBigDecimal("QUANTITY"));
                        customerAmountOldLastYear = customerAmountOldLastYear.add(lastYearWeekOldRs.getBigDecimal("CUSTOMER_AMOUNT"));
                        copiesOldLastYear = copiesOldLastYear.add(lastYearWeekOldRs.getBigDecimal("COPIES"));
                        storeAmountOldLastYear = storeAmountOldLastYear.add(lastYearWeekOldRs.getBigDecimal("STORE_AMOUNT"));
                    }
                    lastYearWeekOldRs.close();
                }
            }
            //合并今年本周集合
            for (int a = 0; a < yearWeekOldList.size(); a++) {
                LinkedHashMap<String, Object> map1 = (LinkedHashMap) yearWeekOldList.get(a);
                String year = (String) map1.get(FIELD_NAME.YEAR.toString());
                String month = (String) map1.get(FIELD_NAME.MONTH.toString());
                String day = (String) map1.get(FIELD_NAME.DAY.toString());
                BigDecimal salesAmount = (BigDecimal) map1.get(FIELD_NAME.SALES_AMOUNT_OLD.toString());
                BigDecimal customerAmount = (BigDecimal) map1.get(FIELD_NAME.CUSTOMER_AMOUNT_OLD.toString());
                BigDecimal copies = (BigDecimal) map1.get(FIELD_NAME.COPIES_OLD.toString());
                BigDecimal storeAmout = (BigDecimal) map1.get(FIELD_NAME.STORE_AMOUNT_OLD.toString());
                for (int b = 0; b < yearWeekOldListForAdd.size(); b++) {
                    LinkedHashMap map2 = (LinkedHashMap) yearWeekOldListForAdd.get(b);
                    if (year.equals(map2.get(FIELD_NAME.YEAR.toString())) && month.equals(map2.get(FIELD_NAME.MONTH.toString())) && day.equals(map2.get(FIELD_NAME.DAY.toString()))) {
                        salesAmount = salesAmount.add((BigDecimal) map2.get(FIELD_NAME.SALES_AMOUNT_OLD.toString()));
                        customerAmount = customerAmount.add((BigDecimal) map2.get(FIELD_NAME.CUSTOMER_AMOUNT_OLD.toString()));
                        copies = copies.add((BigDecimal) map2.get(FIELD_NAME.COPIES_OLD.toString()));
                        storeAmout = storeAmout.add((BigDecimal) map2.get(FIELD_NAME.STORE_AMOUNT_OLD.toString()));
                    }
                }
                map1.put(FIELD_NAME.SALES_AMOUNT.toString(), salesAmount);
                map1.put(FIELD_NAME.CUSTOMER_AMOUNT.toString(), customerAmount);
                map1.put(FIELD_NAME.COPIES.toString(), copies);
                map1.put(FIELD_NAME.STORE_AMOUNT.toString(), storeAmout);
            }
            //合并今年本周、上周、去年本周数据 老店
            for (int i = 0; i < yearWeekOldList.size(); i++) {
                LinkedHashMap<String, Object> map1 = (LinkedHashMap<String, Object>) yearWeekOldList.get(i);
                //本周、上周合并
                // SALES_AMOUNT_LAST_WEEK_ALL, CUSTOMER_AMOUNT_LAST_WEEK_ALL, COPIES_LAST_WEEK_ALL, STORE_AMOUNT_LAST_WEEK_ALL, UNIT_PRICE_LAST_WEEK_ALL,
                map1.put(FIELD_NAME.SALES_AMOUNT_OLD_LAST_WEEK_ALL.toString(), salesAmountOldLastWeek);
                map1.put(FIELD_NAME.CUSTOMER_AMOUNT_OLD_LAST_WEEK_ALL.toString(), customerAmountOldLastWeek);
                map1.put(FIELD_NAME.COPIES_OLD_LAST_WEEK_ALL.toString(), copiesOldLastWeek);
                map1.put(FIELD_NAME.STORE_AMOUNT_OLD_LAST_WEEK_ALL.toString(), storeAmountOldLastWeek);
                //今年本周，去年本周合并
                //SALES_AMOUNT_OLD_LAST_WEEK_ALL, CUSTOMER_AMOUNT_OLD_LAST_WEEK_ALL, COPIES_OLD_LAST_WEEK_ALL, STORE_AMOUNT_OLD_LAST_WEEK_ALL, UNIT_PRICE_OLD_LAST_WEEK_ALL,
                map1.put(FIELD_NAME.SALES_AMOUNT_OLD_LAST_YEAR_ALL.toString(), salesAmountOldLastYear);
                map1.put(FIELD_NAME.CUSTOMER_AMOUNT_OLD_LAST_YEAR_ALL.toString(), customerAmountOldLastYear);
                map1.put(FIELD_NAME.COPIES_OLD_LAST_YEAR_ALL.toString(), copiesOldLastYear);
                map1.put(FIELD_NAME.STORE_AMOUNT_OLD_LAST_YEAR_ALL.toString(), storeAmountOldLastYear);
            }
            //全部，老店合并
            for (int i = 0; i < yearWeekDataList.size(); i++) {
                LinkedHashMap<String, Object> linkedHashMap = (LinkedHashMap) yearWeekDataList.get(i);
                LinkedHashMap<String, Object> row = new LinkedHashMap<String, Object>();
                String year = (String) linkedHashMap.get(FIELD_NAME.YEAR.toString());
                String month = (String) linkedHashMap.get(FIELD_NAME.MONTH.toString());
                String day = (String) linkedHashMap.get(FIELD_NAME.DAY.toString());
                // 注：字段赋值的顺序，要跟枚举里的顺序一样
                row.put(FIELD_NAME.YEAR.toString(), linkedHashMap.get(FIELD_NAME.YEAR.toString()));
                row.put(FIELD_NAME.MONTH.toString(), linkedHashMap.get(FIELD_NAME.MONTH.toString()));
                row.put(FIELD_NAME.DAY.toString(), linkedHashMap.get(FIELD_NAME.DAY.toString()));
                row.put(FIELD_NAME.WEEK_DAY.toString(), linkedHashMap.get(FIELD_NAME.WEEK_DAY.toString()));
                row.put(FIELD_NAME.SALES_AMOUNT.toString(), linkedHashMap.get(FIELD_NAME.SALES_AMOUNT.toString()));
                row.put(FIELD_NAME.CUSTOMER_AMOUNT.toString(), linkedHashMap.get(FIELD_NAME.CUSTOMER_AMOUNT.toString()));
                row.put(FIELD_NAME.COPIES.toString(), linkedHashMap.get(FIELD_NAME.COPIES.toString()));
                row.put(FIELD_NAME.STORE_AMOUNT.toString(), linkedHashMap.get(FIELD_NAME.STORE_AMOUNT.toString()));
                row.put(FIELD_NAME.SALES_AMOUNT_LAST_WEEK_ALL.toString(), linkedHashMap.get(FIELD_NAME.SALES_AMOUNT_LAST_WEEK_ALL.toString()));
                row.put(FIELD_NAME.CUSTOMER_AMOUNT_LAST_WEEK_ALL.toString(), linkedHashMap.get(FIELD_NAME.CUSTOMER_AMOUNT_LAST_WEEK_ALL.toString()));
                row.put(FIELD_NAME.COPIES_LAST_WEEK_ALL.toString(), linkedHashMap.get(FIELD_NAME.COPIES_LAST_WEEK_ALL.toString()));
                row.put(FIELD_NAME.STORE_AMOUNT_LAST_WEEK_ALL.toString(), linkedHashMap.get(FIELD_NAME.STORE_AMOUNT_LAST_WEEK_ALL.toString()));
                row.put(FIELD_NAME.SALES_AMOUNT_LAST_YEAR_ALL.toString(), linkedHashMap.get(FIELD_NAME.SALES_AMOUNT_LAST_YEAR_ALL.toString()));
                row.put(FIELD_NAME.CUSTOMER_AMOUNT_LAST_YEAR_ALL.toString(), linkedHashMap.get(FIELD_NAME.CUSTOMER_AMOUNT_LAST_YEAR_ALL.toString()));
                row.put(FIELD_NAME.COPIES_LAST_YEAR_ALL.toString(), linkedHashMap.get(FIELD_NAME.COPIES_LAST_YEAR_ALL.toString()));
                row.put(FIELD_NAME.STORE_AMOUNT_LAST_YEAR_ALL.toString(), linkedHashMap.get(FIELD_NAME.STORE_AMOUNT_LAST_YEAR_ALL.toString()));
                for (int b = 0; b < yearWeekOldList.size(); b++) {
                    LinkedHashMap map2 = (LinkedHashMap) yearWeekOldList.get(b);
                    if (year.equals(map2.get(FIELD_NAME.YEAR.toString())) && month.equals(map2.get(FIELD_NAME.MONTH.toString())) && day.equals(map2.get(FIELD_NAME.DAY.toString()))) {
                        row.put(FIELD_NAME.SALES_AMOUNT_OLD.toString(), (BigDecimal) map2.get(FIELD_NAME.SALES_AMOUNT_OLD.toString()));
                        row.put(FIELD_NAME.CUSTOMER_AMOUNT_OLD.toString(), (BigDecimal) map2.get(FIELD_NAME.CUSTOMER_AMOUNT_OLD.toString()));
                        row.put(FIELD_NAME.COPIES_OLD.toString(), map2.get(FIELD_NAME.COPIES_OLD.toString()));
                        row.put(FIELD_NAME.STORE_AMOUNT_OLD.toString(), (BigDecimal) map2.get(FIELD_NAME.STORE_AMOUNT_OLD.toString()));
                        row.put(FIELD_NAME.SALES_AMOUNT_OLD_LAST_WEEK_ALL.toString(), map2.get(FIELD_NAME.SALES_AMOUNT_OLD_LAST_WEEK_ALL.toString()));
                        row.put(FIELD_NAME.CUSTOMER_AMOUNT_OLD_LAST_WEEK_ALL.toString(), map2.get(FIELD_NAME.CUSTOMER_AMOUNT_OLD_LAST_WEEK_ALL.toString()));
                        row.put(FIELD_NAME.COPIES_OLD_LAST_WEEK_ALL.toString(), map2.get(FIELD_NAME.COPIES_OLD_LAST_WEEK_ALL.toString()));
                        row.put(FIELD_NAME.STORE_AMOUNT_OLD_LAST_WEEK_ALL.toString(), map2.get(FIELD_NAME.STORE_AMOUNT_OLD_LAST_WEEK_ALL.toString()));
                        row.put(FIELD_NAME.SALES_AMOUNT_OLD_LAST_YEAR_ALL.toString(), map2.get(FIELD_NAME.SALES_AMOUNT_OLD_LAST_YEAR_ALL.toString()));
                        row.put(FIELD_NAME.CUSTOMER_AMOUNT_OLD_LAST_YEAR_ALL.toString(), map2.get(FIELD_NAME.CUSTOMER_AMOUNT_OLD_LAST_YEAR_ALL.toString()));
                        row.put(FIELD_NAME.COPIES_OLD_LAST_YEAR_ALL.toString(), map2.get(FIELD_NAME.COPIES_OLD_LAST_YEAR_ALL.toString()));
                        row.put(FIELD_NAME.STORE_AMOUNT_OLD_LAST_YEAR_ALL.toString(), map2.get(FIELD_NAME.STORE_AMOUNT_OLD_LAST_YEAR_ALL.toString()));
                    }
                }
                if (row.size() < 17) {
                    row.put(FIELD_NAME.SALES_AMOUNT_OLD.toString(), null);
                    row.put(FIELD_NAME.CUSTOMER_AMOUNT_OLD.toString(), null);
                    row.put(FIELD_NAME.COPIES_OLD.toString(), null);
                    row.put(FIELD_NAME.STORE_AMOUNT_OLD.toString(), null);
                    row.put(FIELD_NAME.SALES_AMOUNT_OLD_LAST_WEEK_ALL.toString(), null);
                    row.put(FIELD_NAME.CUSTOMER_AMOUNT_OLD_LAST_WEEK_ALL.toString(), null);
                    row.put(FIELD_NAME.COPIES_OLD_LAST_WEEK_ALL.toString(), null);
                    row.put(FIELD_NAME.STORE_AMOUNT_OLD_LAST_WEEK_ALL.toString(), null);
                    row.put(FIELD_NAME.SALES_AMOUNT_OLD_LAST_YEAR_ALL.toString(), null);
                    row.put(FIELD_NAME.CUSTOMER_AMOUNT_OLD_LAST_YEAR_ALL.toString(), null);
                    row.put(FIELD_NAME.COPIES_OLD_LAST_YEAR_ALL.toString(), null);
                    row.put(FIELD_NAME.STORE_AMOUNT_OLD_LAST_YEAR_ALL.toString(), null);
                }
                rowData.add(row);
            }
            System.out.println(rowData.toString());
            // 释放数据库资源
            stmt.close();
            conn.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    // 释放一些资源，因为可能会有重复调用，所以需释放valueList，将上次查询的结果释放掉
    @Override
    public void release() throws Exception {
        super.release();
        this.rowData = null;
    }
}
